﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace FirmaTransportowa
{
    public class Salary : IModel<Salary>
    {
        [System.ComponentModel.DisplayName("ID wypłaty")]
        public int id { get; set; }
        [System.ComponentModel.DisplayName("ID pracownika")]
        public int worker_id { get; set; }
        [System.ComponentModel.DisplayName("ID zawodu")]
        public int occupation_id { get; set; }
        [System.ComponentModel.DisplayName("Data wypłaty")]
        public DateTime payment_date { get; set; }
        [System.ComponentModel.DisplayName("Kwota")]
        public float amount { get; set; }

        public Salary(int id, int worker_id, int occupation_id, DateTime payment_date, float amount)
        {
            this.id = id;
            this.worker_id = worker_id;
            this.occupation_id = occupation_id;
            this.payment_date = payment_date;
            this.amount = amount;
        }

        public Salary()
        {

        }

        public int ReturnValue
        {
            get
            {
                return this.id;
            }
        }
       
        public int save()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            int createdId = -1;

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                bool newObject = false;

                if (Salary.getById(this.id) == null)
                {
                    cmd.CommandText = "INSERT INTO Salary(worker_ID, occupation_ID, payment_date, amount) VALUES(@worker_id, @occupation_id, @payment_date, @amount) SELECT CONVERT(int, SCOPE_IDENTITY())";
                    newObject = true;
                }
                else
                {
                    cmd.CommandText = "UPDATE Salary SET worker_ID=@worker_ID, occupation_ID=@occupation_id, payment_date=@payment_date, amount=@amount WHERE salary_ID=@id";
                    cmd.Parameters.AddWithValue("@id", this.id);
                }

                cmd.Parameters.AddWithValue("@worker_id", this.worker_id);
                cmd.Parameters.AddWithValue("@occupation_id", this.occupation_id);
                cmd.Parameters.AddWithValue("@payment_date", this.payment_date);
                cmd.Parameters.AddWithValue("@amount", this.amount);
                cmd.Connection = conn;

                if (newObject)
                {
                    createdId = (int)cmd.ExecuteScalar();
                }
                else
                {
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return createdId;
        }

        public static List<Salary> list()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            List<Salary> list = new List<Salary>();
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("SELECT * FROM Salary", conn);
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.Default);

                if (reader.HasRows)
                {
                    while (reader.Read())
                    {
                        Salary salary = Salary.getById(int.Parse(reader["salary_ID"].ToString()));

                        list.Add(salary);
                    }
                }
                reader.Close();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return list;
        }

        public static BindingSource getSalariesForManager()
        {
            SqlConnection connection = SqlConnectionFactory.getConnection();
            BindingSource bindingSource = new BindingSource();

            try
            {
                connection.Open();
                SqlCommand command = new SqlCommand("SELECT s.salary_ID, w.name, w.surname, o.name, s.payment_date, s.amount FROM Salary s JOIN Worker w ON w.worker_ID = s.worker_ID JOIN Occupation o ON o.occupation_ID = w.occupation_ID", connection);
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                DataTable dataTable = new DataTable();
                dataAdapter.Fill(dataTable);

                bindingSource.DataSource = dataTable;
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + e.StackTrace);
            }
            finally
            {
                connection.Close();
            }

            return bindingSource;
        }

        public static Salary getById(int id)
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                if (id < 0)
                {
                    throw new ArgumentException();
                }
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "SELECT * FROM Salary WHERE salary_ID=@id;";
                cmd.Parameters.AddWithValue("@id", id);
                cmd.Connection = conn;
                SqlDataReader reader = cmd.ExecuteReader(System.Data.CommandBehavior.SingleRow);

                if (reader.HasRows)
                {
                    reader.Read();
                    Salary salary = new Salary();
                    salary.id = int.Parse(reader["salary_ID"].ToString());
                    salary.worker_id = int.Parse(reader["worker_ID"].ToString());
                    salary.occupation_id = int.Parse(reader["occupation_ID"].ToString());
                    salary.payment_date = DateTime.Parse(reader["payment_date"].ToString());
                    salary.amount = float.Parse(reader["amount"].ToString());

                    conn.Close();
                    return salary;
                }
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }

            return null;

        }

        public void delete()
        {
            SqlConnection conn = SqlConnectionFactory.getConnection();

            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.CommandText = "DELETE FROM Salary WHERE salary_ID=@id";
                cmd.Parameters.AddWithValue("@id", this.id);

                cmd.Connection = conn;
                cmd.ExecuteNonQuery();
            }
            catch (Exception e)
            {
                MessageBox.Show(e.Message + "\n" + e.StackTrace);
            }
            finally
            {
                conn.Close();
            }
        }
    }
}
